package com.cbarmstrong.Finance;

import java.io.BufferedReader;
import java.io.DataOutputStream;
import java.io.InputStreamReader;
import java.net.Socket;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Vector;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.jfree.data.xy.OHLCDataItem;

public class Data {
	
	static Logger logger=Data.getLogger("GA");

	public static Logger getLogger(Object object){
		return getLogger(object.getClass().getName());
	}
	
	public static Logger getLogger(){
		return logger;
	}
	
	public static Connection initConnection() throws SQLException{
		return DriverManager.getConnection(System.getProperty("connectString"));
	}
	
	public static Connection initConnection(String key) throws SQLException{
		return DriverManager.getConnection(System.getProperty(key+"connection"));
	}
	
	public static Logger getLogger(String strObjName)
	{
		try
		{
			
			ColinFormatter simpFormatter = new ColinFormatter();

			String fileName = "/opt/FinanceTools/logs/" + strObjName + ".log";
			FileHandler fileHandler =	
				new FileHandler(fileName, 102400000, 5, true);
			fileHandler.setLevel(Level.ALL);
			fileHandler.setFormatter(simpFormatter);

			Logger logger = Logger.getLogger(strObjName);
			//logger.getHandlers();
			logger.setUseParentHandlers(false);
			logger.addHandler(fileHandler);
			if(System.getProperty("debug", "false").compareTo("true")==0){ logger.setLevel(Level.FINE); }

			return logger;

		}

		catch (Exception ex)
		{
			//if no logging what to do. just printout for now.
			ex.printStackTrace();
			return null;
		}

	}
	
	//@SuppressWarnings({ "rawtypes", "unchecked" })
/*	public static TrainingSet createInputSet(Vector<Vector> inputs, Vector<Vector> outputs, int start, int end){
		logger.info("Entering createTrainingSet: "+start+" to "+end);
		logger.info("Inputs: "+inputs.size()+", Outputs: "+outputs.size());
		String msg="Inputs sizes: ";
		for(int i=0;i<inputs.size();i++){
			msg=msg+inputs.get(i).size()+" ";
		}
		logger.info(msg);
		msg="Outputs sizes: ";
		for(int i=0;i<outputs.size();i++){
			msg=msg+outputs.get(i).size()+" ";
		}
		logger.info(msg);
		TrainingSet returnSet=new TrainingSet(inputs.size(), outputs.size());
		for(int i=start;i<end; i++){
			double[] inputArray=new double[inputs.size()];
			double[] outputArray=new double[outputs.size()];
			//logger.info("Iteration) "+i+" - ");
			for(int j=0;j<inputs.size();j++){
				//logger.info("i:"+j+" ");
				inputArray[j]=(Double) inputs.get(j).get(i);
			}
			for(int j=0;j<outputs.size();j++){
				//logger.info("o:"+j+" ");
				if (outputs.get(j).get(i)!=null){ outputArray[j]=(Double) outputs.get(j).get(i); }
			}
			//logger.info();
			returnSet.addElement(new SupervisedTrainingElement(inputArray, outputArray));
			//System.out.print(Arrays.toString(inputArray));
			//logger.info(Arrays.toString(outputArray));
		}
		return returnSet;
	}*/
	
	public static Vector<OHLCDataItem> getPrices(String name) throws SQLException{
		Vector<OHLCDataItem> retVect=new Vector<OHLCDataItem>();
		Connection connect=initConnection();
		Statement statement=connect.createStatement();
		ResultSet results=statement.executeQuery("SELECT * FROM prices WHERE stockid=(SELECT id FROM stock WHERE name='"+name+"') ORDER BY date DESC");
		while(results.next()){
			retVect.insertElementAt(new OHLCDataItem(results.getDate("date"),results.getDouble("open"),results.getDouble("high"),results.getDouble("low"),results.getDouble("close"),results.getDouble("volume")), 0);
		}
		connect.close();
		logger.info("Got data from "+new SimpleDateFormat("dd-MM-yyyy").format(retVect.firstElement().getDate())
				+" to "+new SimpleDateFormat("dd-MM-yyyy").format(retVect.lastElement().getDate()));
		return retVect;
	}
	

	public static double getSpread(String name) throws SQLException{
		Connection connect=initConnection();
		Statement s = connect.createStatement();
		ResultSet r = s.executeQuery("SELECT spread FROM stock WHERE name='"+name+"'");
		if(r.first()){ return r.getDouble(1); }
		return 0;
	}
	
	public static void storeHistoric(String name, int days) throws SQLException{
		Connection connect=initConnection();
		Statement statement=connect.createStatement();
		Statement u = connect.createStatement();
		logger.fine("UPDATE locks SET value=1 WHERE key_name='PR "+name+"' and value=0");
		while(u.executeUpdate("UPDATE locks SET value=1 WHERE key_name='PR "+name+"' and value=0")==0){ 
			try {
				Thread.sleep(5000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			} 
		}
		ResultSet results=statement.executeQuery("SELECT yTicker, gTicker, preferred FROM stock WHERE name='"+name+"'");
		if(!results.first()){ logger.warning("Empty result set for "+name); return; }
		Calendar startDate=Calendar.getInstance();
        startDate.add(Calendar.DAY_OF_MONTH, days*-1);
		Vector<OHLCDataItem> prices=new Vector<OHLCDataItem>();
		if(results.getString("preferred").compareTo("yTicker")==0){ prices=getAllYahooPrices(results.getString("yTicker"), Calendar.getInstance(), startDate); }
		if(results.getString("preferred").compareTo("gTicker")==0){ prices=getAllGooglePrices(results.getString("gTicker"), Calendar.getInstance(), startDate); }
		PreparedStatement prep1=connect.prepareStatement("INSERT INTO prices VALUES (default, (SELECT id from stock WHERE name='"+name+"'), ?, ?, ?, ?, ?, ? ) ");
		PreparedStatement prep2=connect.prepareStatement("UPDATE prices set close=?, open=?, high=?, low=? where stockid=(SELECT id from stock WHERE name='"+name+"') AND date=?");
		Statement check=connect.createStatement();
		check.execute("DELETE FROM prices WHERE datediff('"+new java.sql.Date(startDate.getTimeInMillis())+"',date)>0 AND stockid=(SELECT id from stock WHERE name='"+name+"')");
		for (int i=0;i<prices.size();i++){
			try{
			results=check.executeQuery("SELECT * FROM prices WHERE date='"+new java.sql.Date(prices.get(i).getDate().getTime())+"' AND stockid=(SELECT id from stock WHERE name='"+name+"')");
			if(results.first()){ 
				prep2.setDouble(1, (Double) prices.get(i).getClose());
				prep2.setDouble(2, (Double) prices.get(i).getOpen());
				prep2.setDouble(3, (Double) prices.get(i).getHigh());
				prep2.setDouble(4, (Double) prices.get(i).getLow());
				prep2.setDate(5, new java.sql.Date(prices.get(i).getDate().getTime()));
				prep2.executeUpdate();
				//logger.fine("UPDATE prices set close=?, open=?, high=?, low=? where stockid=(SELECT id from stock WHERE name='"+name+"') AND date='"+new java.sql.Date(prices.get(i).getDate().getTime())+"'");
				//logger.fine("Executed price update: "+lines+" lines updated");
			}
			else{
				prep1.setDouble(1, (Double) prices.get(i).getOpen());
				prep1.setDouble(2, (Double) prices.get(i).getClose());
				prep1.setDouble(3, (Double) prices.get(i).getHigh());
				prep1.setDouble(4, (Double) prices.get(i).getLow());
				prep1.setDouble(5, (Double) prices.get(i).getVolume());
				prep1.setDate(6, new java.sql.Date(prices.get(i).getDate().getTime()));
				prep1.execute();
			}
			} catch (com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException e){ i--;	}
			catch(java.sql.SQLException e){ i--; }
		}
		u.executeUpdate("UPDATE locks SET value=0 WHERE key_name='PR "+name+"' and value=1");
		connect.close();
	}
	
	public static Vector<OHLCDataItem> getAllYahooPrices(String y_Ticker, Calendar date, Calendar startDate){
    	Vector<OHLCDataItem> retVect=new Vector<OHLCDataItem>();
    	try{
    		logger.fine("Opening socket to ichart.finance.yahoo.com");
			Socket socket=new Socket("ichart.finance.yahoo.com",80);
			DataOutputStream os=new DataOutputStream(socket.getOutputStream());
			BufferedReader is=new BufferedReader(new InputStreamReader(socket.getInputStream()));
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String e = new SimpleDateFormat("dd").format(date.getTime());
			int d = new Integer(new SimpleDateFormat("MM").format(date.getTime()))-1;
			String f = new SimpleDateFormat("yyyy").format(date.getTime());
			String b = new SimpleDateFormat("dd").format(startDate.getTime());
			int a = new Integer(new SimpleDateFormat("MM").format(startDate.getTime()))-1;
			String c = new SimpleDateFormat("yyyy").format(startDate.getTime());
    		logger.fine("Requesting: GET /table.csv?s="+y_Ticker+"&a="+a+"&b="+b+"&c="+c+"&d="+d+"&e="+e+"&f="+f+"&g=d&ignore=.csv HTTP/1.0"); 
			os.writeBytes("GET /table.csv?s="+y_Ticker+"&a="+a+"&b="+b+"&c="+c+"&d="+d+"&e="+e+"&f="+f+"&g=d&ignore=.csv HTTP/1.0\r\n\r\n");
			os.flush();
			String line=new String();
			boolean t = false;
			while ((line=is.readLine())!=null){
				String[] values=line.split(",");
				if(t){ 
					if(new Double(values[1])==0 || new Double(values[2])==0 || new Double(values[3])==0 || new Double(values[4])==0){
						OHLCDataItem di = retVect.get(0);
						retVect.insertElementAt(new OHLCDataItem(df.parse(values[0]),new Double((Double) di.getOpen()), new Double((Double) di.getHigh()), new Double((Double) di.getLow()), new Double((Double) di.getClose()), new Double((Double) di.getVolume())), 0);
					} else{
						retVect.insertElementAt(new OHLCDataItem(df.parse(values[0]),new Double(values[1]),new Double(values[2]),new Double(values[3]),new Double(values[4]),new Double(values[5])), 0);
					}
				}
				if(values[0].compareTo("Date")==0){ t=true; }
			}
    		logger.fine("Got "+retVect.size()+" points"); 
    		logger.fine("Most recent data from: "+new SimpleDateFormat("dd-MM-yyyy").format(retVect.lastElement().getDate()));
    		logger.fine("Opening socket to download.finance.yahoo.com"); 
			socket=new Socket("download.finance.yahoo.com",80);
			os=new DataOutputStream(socket.getOutputStream());
			is=new BufferedReader(new InputStreamReader(socket.getInputStream()));
    		logger.fine("Requesting: GET /d/quotes.csv?s="+y_Ticker+"&f=sl1d1t1c1ohgv&e=.csv HTTP/1.0"); 
			os.writeBytes("GET /d/quotes.csv?s="+y_Ticker+"&f=sl1d1t1c1ohgv&e=.csv HTTP/1.0\r\n\r\n");
			os.flush();
			df = new SimpleDateFormat("MM/dd/yyyy");
    		logger.fine("Reading data....");
    		while(true){
    			if ((line=is.readLine())==null){ logger.warning("Failed to get latest day's data: "+line); System.exit(8); }
    			if (line.contains(y_Ticker)){ break; }
    		}
    		logger.fine("Got: "+line); 
			String[] values=line.split(",");
			values[2]=values[2].replace("\"", "");
    		logger.fine("Date length: "+values[0].length()); 
			if( values[0].length()!=10 ){ values[0]="0"+values[0]; }
			if(new Double(values[5])==0 || new Double(values[6])==0 || new Double(values[7])==0 || new Double(values[1])==0){
				OHLCDataItem di = retVect.get(0);
				retVect.add(new OHLCDataItem(df.parse(values[2]),new Double((Double) di.getOpen()), new Double((Double) di.getHigh()), new Double((Double) di.getLow()), new Double((Double) di.getClose()), new Double((Double) di.getVolume())));
			} else{
				retVect.add(new OHLCDataItem(df.parse(values[2]),new Double(values[5]),new Double(values[6]),new Double(values[7]),new Double(values[1]),new Double(values[8])));
			}
    		logger.fine("Updated to "+retVect.size()+" points");
		}
		catch(java.io.IOException io){
			logger.severe("IO Exception opening streams");
			logger.severe(io.getMessage());
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
    	return retVect;
    }

	public static Vector<OHLCDataItem> getAllPrices(String ticker, Calendar date, String start){
		String t=ticker.split("-")[1];
		Calendar startDate=Calendar.getInstance();
		try {
			startDate.setTime(new SimpleDateFormat("dd-MMM-yy").parse(start));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		if(ticker.split("-")[0].compareTo("yahoo")==0){ return getAllYahooPrices(t,date,startDate); }
		if(ticker.split("-")[0].compareTo("google")==0){ return getAllGooglePrices(t,date,startDate); }
		return null;
	}
	
	/*public static Vector<Vector> getNetParms(Ticker t, String string) {
		String[] parms=string.split(":");
		Vector<Vector> returns=new Vector<Vector>();
		for(int i=0;i<parms.length;i++){
			logger.info("Getting indicator "+i+") "+parms[i]);
			Vector<Double> passed=null;
			if(parms[i].split(",")[1].indexOf("prev-")==0){ 
				passed=t.getIndicator(parms[new Integer(parms[i].split(",")[1].substring(5))]); 
			}
			returns.add(t.getIndicator(parms[i], passed));
		}
		return returns;
	}*/
	
	public static Vector<OHLCDataItem> getAllGooglePrices(String g_Ticker, Calendar date, Calendar startDate){
    	String end=new String(), start=new String();
    	Vector<OHLCDataItem> retVect=new Vector<OHLCDataItem>();
    	try{
			Socket socket=new Socket("www.google.co.uk",80);
			DataOutputStream os=new DataOutputStream(socket.getOutputStream());
			BufferedReader is=new BufferedReader(new InputStreamReader(socket.getInputStream()));
			DateFormat df = new SimpleDateFormat("dd-MMM-yy");
			end=URLEncoder.encode(df.format(date.getTime()),"UTF-8");
    		start=URLEncoder.encode(df.format(startDate.getTime()),"UTF-8");
			os.writeBytes("GET /finance/historical?q="+g_Ticker+"&startdate="+start+"&enddate="+end+"&output=csv HTTP/1.0\r\n\r\n");
			os.flush();
			String line=new String();
			boolean t = false;
			while ((line=is.readLine())!=null){
				String[] values=line.split(",");
				if(t){ 
					if(new Double(values[1])==0 || new Double(values[2])==0 || new Double(values[3])==0 || new Double(values[4])==0){
						OHLCDataItem di = retVect.get(0);
						retVect.insertElementAt(new OHLCDataItem(df.parse(values[0]),new Double((Double) di.getOpen()), new Double((Double) di.getHigh()), new Double((Double) di.getLow()), new Double((Double) di.getClose()), new Double((Double) di.getVolume())), 0);
					} else{
						retVect.insertElementAt(new OHLCDataItem(df.parse(values[0]),new Double(values[1]),new Double(values[2]),new Double(values[3]),new Double(values[4]),new Double(values[5])), 0);
					}
				}
				if(line.indexOf("Date")>0){ t=true; }
			}
		}
		catch(java.io.IOException io){
			logger.info("IO Exception opening streams");
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
    	return retVect;
    }
	
	public static Vector<Double> normalisePriceSet(Vector<OHLCDataItem> highLow, Vector<Double> series, Vector<Double> np, int periods){
		np.clear();
		double highestHigh=(Double) highLow.lastElement().getHigh(), lowestLow=(Double) highLow.lastElement().getLow();
		if(periods==0){ periods=highLow.size(); }
		for(int i=highLow.size()-periods;i<highLow.size();i++){
			if((Double)highLow.get(i).getLow()<lowestLow){ lowestLow=(Double) highLow.get(i).getLow(); }
			if((Double)highLow.get(i).getHigh()>highestHigh){ highestHigh=(Double) highLow.get(i).getHigh(); }
		}
		for(int i=series.size()-periods;i<series.size();i++){
			if(highestHigh-lowestLow==0 || series.get(i)==null){ np.add(null); }
			else{ np.add((series.get(i)-lowestLow)/(highestHigh-lowestLow)); }
		}
		return np;
	}
	
	public static Vector<Double> generateLevel(int reference, double value){
		Vector<Double> retVect=new Vector<Double>();
		for(int i=0;i<reference; i++){
			retVect.add(value);
		}
		return retVect;
	}
	
	public static Vector<Double> weightedClose(Vector<OHLCDataItem> prices){
		Vector<Double> wc=new Vector<Double>();
		for(int i=0;i<prices.size();i++){
			wc.add(((Double) prices.get(i).getHigh()+(Double) prices.get(i).getLow()+(2.0*(Double) prices.get(i).getClose()))/4.0);
		}
		return wc;
	}
	
	public static Vector<Double> unNormalisePriceSet(Vector<OHLCDataItem> highLow, Vector<Double> series, Vector<Double> np, int periods){
		series.clear();
		double highestHigh=(Double) highLow.lastElement().getHigh(), lowestLow=(Double) highLow.lastElement().getLow();
		if(periods==0){ periods=highLow.size(); }
		for(int i=highLow.size()-periods;i<highLow.size();i++){
			if((Double)highLow.get(i).getLow()<lowestLow){ lowestLow=(Double) highLow.get(i).getLow(); }
			if((Double)highLow.get(i).getHigh()>highestHigh){ highestHigh=(Double) highLow.get(i).getHigh(); }
		}
		for(int i=np.size()-periods;i<np.size();i++){
			series.add((np.get(i)*(highestHigh-lowestLow)+lowestLow));
		}
		return series;
	}
	
	public static Vector<Double> normaliseDoubleSet(Vector<Double> highLow, Vector<Double> series, Vector<Double> np, int periods){
		np.clear();
		double highestHigh=(Double) highLow.lastElement(), lowestLow=(Double) highLow.lastElement();
		if(periods==0){ periods=highLow.size(); }
		for(int i=highLow.size()-periods;i<highLow.size();i++){
			if(highLow.get(i)==null){ continue; }
			if((Double)highLow.get(i)<lowestLow){ lowestLow=(Double) highLow.get(i); }
			if((Double)highLow.get(i)>highestHigh){ highestHigh=(Double) highLow.get(i); }
		}
		for(int i=series.size()-periods;i<series.size();i++){
			if(highestHigh-lowestLow==0 || series.get(i)==null){ np.add(null); }
			else{ np.add((series.get(i)-lowestLow)/(highestHigh-lowestLow)); }
		}
		return np;
	}
	
	public static Vector<Double> unNormaliseDoubleSet(Vector<Double> highLow, Vector<Double> series, Vector<Double> np, int periods){
		series.clear();
		double highestHigh=(Double) highLow.lastElement(), lowestLow=(Double) highLow.lastElement();
		if(periods==0){ periods=highLow.size(); }
		for(int i=highLow.size()-periods;i<highLow.size();i++){
			if(highLow.get(i)==null){ continue; }
			if((Double)highLow.get(i)<lowestLow){ lowestLow=(Double) highLow.get(i); }
			if((Double)highLow.get(i)>highestHigh){ highestHigh=(Double) highLow.get(i); }
		}
		for(int i=np.size()-periods;i<np.size();i++){
			if(i<0){ series.add(null);  }
			else if(highestHigh-lowestLow==0 || np.get(i)==null){ series.add(null); }
			else{ series.add(np.get(i)*(highestHigh-lowestLow)+lowestLow); }
		}
		return series;
	}

	public static Vector<Double> differenceSet(Vector<Double> higher, Vector<Double> lower, Vector<Double> difference){
		difference.clear();
		if(higher.size()!=lower.size()){ logger.warning("No diffs - "+higher.size()+", "+lower.size()); return null; }
		for(int i=0;i<higher.size();i++){
			if(higher.get(i)==null || lower.get(i)==null){ difference.add(null); }
			else{ difference.add(higher.get(i)-lower.get(i)); }
		}
		return difference;
	}
	
	@SuppressWarnings("unchecked")
	public static Vector<Double> vectorRight(Vector<Double> set, int offset){
		Vector<Double> newSet = (Vector<Double>) set.clone();
		for(int i=0;i<offset;i++){
			newSet.insertElementAt(null, 0);
			newSet.removeElementAt(newSet.size()-1);
		}
		return newSet;
	}
	
	@SuppressWarnings("unchecked")
	public static Vector<Double> vectorLeft(Vector<Double> set, int offset){
		Vector<Double> newSet = (Vector<Double>) set.clone();
		for(int i=0;i<offset;i++){
			newSet.removeElementAt(0);
			newSet.add(null);
		}
		return newSet;
	}
	
	public static Vector<Double> calculateDeltas(Vector<Double> set, Vector<Double> delta, int offset){
		Vector<Double> newSet=vectorRight(set,offset);
		delta.clear();
		for(int i=0;i<set.size();i++){
			if(set.get(i)==null || newSet.get(i)==null){ delta.add(null); }
			else{ delta.add(set.get(i)-newSet.get(i)); }
		}
		return delta;
	}

	public static Vector<Double> addVectors(Vector<Double> vect1, Vector<Double> vect2) {
		Vector<Double> combined=new Vector<Double>();
		while(vect1.size()>vect2.size()){ vect2.insertElementAt(null, 0); }
		while(vect1.size()<vect2.size()){ vect1.insertElementAt(null, 0); }
		for(int i=0;i<vect1.size();i++){
			if(vect1.get(i)==null || vect2.get(i)==null){ combined.add(null); }
			else{ combined.add(vect1.get(i)+vect2.get(i)); }
		}
		return combined;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static Vector randomiseVector(Vector vect){
		Vector v=new Vector();
		Vector o=(Vector) vect.clone();
		while(o.size()>0){
			v.add(o.get((int) Math.random()*(o.size()-1)));
		}
		return v;
	}
	
	public static Double getStDev(Vector<Double> vect){
		Vector<Double> bestFit=new Vector<Double>();
		bestFit.add(0.0);
		for(int i=1;i<vect.size();i++){
			bestFit.add(bestFit.get(i-1)+(vect.lastElement()/vect.size()));
		}
		Double variance=0.0;
		for(int i=0;i<vect.size();i++){
			Double diff = vect.get(i)-bestFit.get(i);
			variance+=diff*diff;
		}
		return Math.sqrt(variance/vect.size());
	}

	
}
